package com.ks.sgms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ks.sgms.entity.Department;
import com.ks.sgms.entity.Score;
import com.ks.sgms.util.DbHelper;

public class DepartmentDao {
	public List<Department> getAll() { 
		List<Department> list = new ArrayList<Department>(); 
		Connection conn = DbHelper.getConnection();
		String sql1 = "select department.department_id,major_id,major_name,department_name"
				+ " from department,major"
				+ " where department.department_id = major.department_id"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql1);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Department d = new Department();
				d.department_id = Integer.parseInt(rst.getString("department_id"));
				d.department_name = rst.getString("department_name");
				d.major_id = rst.getString("major_id");
				d.major_name = rst.getString("major_name");
				//求专业人数
				String sql2 = "select c.major_id ,s.class_id ,count(student_id) count "
						+ "from student s,class c "
						+ "where s.class_id  = c.class_id  "
						+ "and c.major_id  = ?"
						+ "group by c.major_id";
				PreparedStatement pst1 = conn.prepareStatement(sql2);
				pst1.setString(1, d.major_id);
				ResultSet rst1 = pst1.executeQuery();
				if(rst1.next()) {
					d.allPersonNumber = Integer.parseInt(rst1.getString("count"));
				}
				list.add(d);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace(); 
		}
		return list;
	}
	
	public boolean updateMajor(Department department) {
		String sql = "UPDATE major SET major_id=?,major_name=?,department_id=? where major_id= ?";
		Connection connection = DbHelper.getConnection();
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, department.major_id);
			pst.setString(2, department.major_name);
			pst.setString(3, department.department_id+"");
			pst.setString(4, department.major_id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public Department selectMajorById(String major_id) {
		Connection conn = DbHelper.getConnection();
		Department score = new Department();
		String sql = "select * from major where major_id = ?"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, major_id);
			ResultSet rst = pst.executeQuery();
			if (rst.next()) {
				score.major_id = rst.getString("major_id");
				score.major_name = rst.getString("major_name");
				score.department_id = Integer.parseInt(rst.getString("department_id"));
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return score;
	}
	
	public boolean addMajor(Department s) {
		String sql = "INSERT INTO major(major_id,major_name,department_id) VALUES (?,?,?)"; 
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, s.major_id);
			pst.setString(2, s.major_name);
			pst.setString(3, s.department_id+"");
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean deleteMajor(String major_id) {
		String sql = "DELETE FROM major WHERE major_id = ?";
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, major_id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false; 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
